from absl import app, flags
import pandas as pd
import MySQLdb
from sqlalchemy import create_engine
import tempfile

import coin.base.database.util as db_util

FLAGS = flags.FLAGS

COIN_META_TABLES = ['ExchangeAPI', 'Account', 'Strategy']
STRATEGY_META_TABLE = ['exchange_api', 'account', 'strategy']


def _gen_strategy_meta_engine():
  engine = create_engine(
      'mysql+mysqldb://pta_querier:0C3n5szBUEhn@coin-db.corp.prestolabs.io/strategy_meta_20190715',
      echo=False)
  return engine


def _gen_coin_meta_conn(db_config_path):
  db_config = db_util.read_db_config(db_config_path)
  conn = MySQLdb.connect(host=db_config.get('host', None),
                         user=db_config.get('user', None),
                         passwd=db_config.get('password', None),
                         db=db_config.get('database', None),
                         local_infile=1)
  return conn


def _fetch_update_sql(table, path):
  if table == 'Account':
    sql = """LOAD DATA LOCAL INFILE '%s' 
REPLACE INTO TABLE Account 
FIELDS ENCLOSED BY '"' TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES ( 
account_id,account_group,exchange_api_id,owner,active,note);"""
  elif table == 'ExchangeAPI':
    sql = """LOAD DATA LOCAL INFILE '%s'
REPLACE INTO TABLE ExchangeAPI
FIELDS ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES (
exchange_api_id,country,market_type,exchange,api_version,primary_api_version,active,note);"""
  elif table == 'Strategy':
    sql = """LOAD DATA LOCAL INFILE '%s'
REPLACE INTO TABLE Strategy
FIELDS ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES (
strategy_id,strategy_name,strategy_group,trader,active,note);"""
  else:
    raise NotImplementedError(table)
  return sql % path


def _fetech_data_sql(table):
  if table == 'exchange_api':
    sql = "select * from exchange_api"
  elif table == 'account':
    sql = "select a.*, b.account_group_name as account_group from account a, account_group b where a.account_group_id=b.account_group_id"
  elif table == 'strategy':
    sql = "select a.*, b.strategy_group_name as strategy_group from strategy a, strategy_group b where a.strategy_group_id=b.strategy_group_id"
  else:
    raise NotImplementedError(table)
  return sql


def update_table(table, path):
  sql = _fetch_update_sql(table, path)
  conn = _gen_coin_meta_conn(FLAGS.coin_meta_config)
  cursor = conn.cursor()
  cursor.execute(sql)
  conn.commit()
  conn.close()


def main(_):
  for coin_meta_table, strategy_meta_table in zip(COIN_META_TABLES, STRATEGY_META_TABLE):
    sql = "select * from %s" % coin_meta_table
    conn = _gen_coin_meta_conn(FLAGS.coin_meta_config)
    coin_meta_data = pd.read_sql(sql, conn)
    sql = _fetech_data_sql(strategy_meta_table)
    engine = _gen_strategy_meta_engine()
    start_meta_data = pd.read_sql(sql, engine)
    for col in coin_meta_data.columns:
      if col not in start_meta_data.columns:
        start_meta_data[col] = coin_meta_data[col]
    update_data = start_meta_data[coin_meta_data.columns]
    update_data.replace({None: 'NULL'}, inplace=True)
    with tempfile.TemporaryDirectory() as tmpdirname:
      update_data.to_csv(tmpdirname + '/update_data.csv', index=False)
      update_table(coin_meta_table, tmpdirname + '/update_data.csv')
    print("sync %s successfully" % coin_meta_table)


if __name__ == '__main__':
  flags.DEFINE_string('coin_meta_config', None, 'coin meta config path')

  app.run(main)
